package d1023;
import java.sql.Statement;
        import java.sql.SQLException;
        import java.sql.PreparedStatement;
        import java.sql.ResultSet;
        import java.sql.Connection;
        import java.sql.Date;
        import java.sql.DriverManager;
@SuppressWarnings("all")
public class JdbcDemo {
    public static void main(String[] args) throws ClassNotFoundException, SQLException {
        JdbcDemo demo = new JdbcDemo();
        demo.queryByName("SMITH");
        System.out.println("===================SQL注入攻击=============");
        demo.queryByName("  ' or '1'='1");

        System.out.println("===============预编译语句对象方法===================");
        demo.queryByName("SMITH");
        System.out.println("===================SQL注入攻击=============");
        demo.queryByName("  ' or '1'='1");

        System.out.println("==================交换部门信息================");
        demo.changeLoc(99,42);
    }

    public void text() throws ClassNotFoundException,SQLException{
        //加载驱动
        Class.forName("oracle.jdbc.driver.OracleDriver");
        //创建连接
        Connection conn = DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:orcl","scott","a");
        //构建语句
        Statement stat = ((Connection) conn).createStatement();
        //执行语句
        String sql = "insert into dept (depto,dname,loc) values(99,'财务部','衡阳'）";
        //executeUpdate  除了查询其他语句都可以执行
        int count = stat.executeUpdate(sql);
        System.out.println("新增类"+count+"个部门");
        //关闭资源
        stat.close();
        conn.close();
    }

    public void queryByName(String ename) throws ClassNotFoundException,SQLException{
        //加载驱动
        Class.forName("oracle.jdbc.driver.OracleDriver");
        //创建连接
        Connection conn = DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:orcl","scott","a");
        //构建语句
        Statement stat = conn.createStatement();
        String sql = "select*from emp where ename = '" + ename + "'";
        //执行语句
        ResultSet rs = stat.executeQuery(sql);
        //对结果集遍历
        //next 控制游标（记录指针）向下走，如果有下一行数据，返回TRUE，否则FALSE
        while (rs.next()){
            int empno = rs.getInt(1);
            String eName = rs.getString("ENAME");
            Date hiredate = rs.getDate("HIREDATE");
            System.out.printf("%10d  %20s  %s \n",empno,eName,hiredate);
        }
        //关闭资源
        rs.close();
        stat.close();
        conn.close();
    }

    public void queryByName1(String ename) throws ClassNotFoundException,SQLException{
        //加载驱动
        Class.forName("oracle.jdbc.driver.OracleDriver");
        //创建连接
        Connection conn = DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:orcl","scott","a");
        //=====================================区别============================
        //构建语句
        //? 表示参数占位符
        String sql = "select*from emp where ename = ?";
        // 构建预编译语句对象，它是普通语句对象的子类
        PreparedStatement stat = conn.prepareStatement(sql);
        //设置参数，替代对应的 ？ 占位符
        stat.setNString(1,ename);
        //执行语句
        ResultSet rs = stat.executeQuery();
        //=====================================================================
        //对结果集遍历
        //next 控制游标（记录指针）向下走，如果有下一行数据，返回TRUE，否则FALSE
        while (rs.next()){
            int empno = rs.getInt(1);
            String eName = rs.getString("ENAME");
            Date hiredate = rs.getDate("HIREDATE");
            System.out.printf("%10d  %20s  %s   \n",empno,eName,hiredate);
        }

        //关闭资源
        rs.close();
        stat.close();
        conn.close();
    }

    public void changeLoc(int dept1,int dept2){
        Connection conn = null;
        try{
            //加载驱动
            Class.forName("oracle.jdbc.driver.OracleDriver");
            //创建连接
            conn = DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:orcl","scott","a");
            //关闭自动提交功能
            conn.setAutoCommit(false);

            String selectSql = "select loc from dept where deptno = ?";
            String updateSql = "update dept set loc = ? where deptno = ?";

            PreparedStatement p1 = conn.prepareStatement(selectSql);
            PreparedStatement p2 = conn.prepareStatement(updateSql);
            //查询第一个部门的loc
            p1.setInt(1,dept1);
            ResultSet rs = p1.executeQuery();
            rs.next();
            String loc1 = rs.getString("LOC");
            rs.close();

            //查询第二个部门的loc
            p1.setInt(1,dept1);
            rs = p1.executeQuery();
            rs.next();
            String loc2 = rs.getString("LOC");//重新给rs赋值，新的结果集
            rs.close();

            //更新第一个部门的地址为第二个部门原地址
            p2.setNString(1,loc2);
            p2.setInt(2,dept1);
            p2.executeUpdate();

            //更新第二个部门的地址为第一个部门原地址
            p2.setNString(1,loc1);
            p2.setInt(2,dept2);
            p2.executeUpdate();
            //所有业务全部成功执行完之后，手动提交
            conn.commit();

        }catch(Exception e){
            //如果出现异常，回滚全部操作
            try{
                conn.rollback();
            }catch(SQLException e1){
                e1.printStackTrace();
            }
            e.printStackTrace();
        }finally {
            //关闭连接同时会关闭连接创建语句对象和结果对象
            try{
                if (conn!=null){
                    conn.close();
                }
            }catch (SQLException e){
                e.printStackTrace();
            }

        }


    }

}

